package modele;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashSet;
import java.util.TreeSet;

public class DAO {

    public static Collection<Dep> getlesDeps() {

        Collection<Dep> lesDeps = new HashSet<Dep>();

        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery("select distinct departement from medecin");

            while (rs.next()) {
                String numDep = rs.getString(1);
                lesDeps.add(new Dep(numDep));
            }
//                Statement req2;
//                req2 = con.createStatement();
//                ResultSet rs2 = req2.executeQuery("select * from  medecin where departement=" + numDep );
//                Collection<Med> Colmedecin = new HashSet<Med>();
//                while (rs2.next()) {
//                    String id = rs2.getString("id");
//                    String nom = rs2.getString("nom");
//                    String prenom = rs2.getString("prenom");
//                    String adresse = rs2.getString("adresse");
//                    String tel = rs2.getString("tel");
//                    String spe = rs2.getString("specialitecomplementaire");
//                   Colmedecin.add(new Med(nom,prenom,adresse,tel,spe,id));   
//                }                   
//                 rs2.close();    
            rs.close();
            req.close();

        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return lesDeps;
    }

    public static Collection<Spe> getlesSpes() {

        Collection<Spe> lesSpes = new TreeSet<Spe>();

        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery("select distinct specialitecomplementaire from medecin"
                    + " where specialitecomplementaire is not null;");
            while (rs.next()) {
                Spe s = new Spe(rs.getString("specialitecomplementaire"));
                lesSpes.add(s);
            }

        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return lesSpes;



    }

    public static Collection<Med> getLesMeds() {

        Collection<Med> lesMeds = new TreeSet<Med>();

        try {

            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs;
            rs = req.executeQuery("select * from medecin");
            while (rs.next()) {
                String id = rs.getString("id");
                String nom = rs.getString("nom");
                String prenom = rs.getString("prenom");
                String adresse = rs.getString("adresse");
                String tel = rs.getString("tel");
                String dep = rs.getString("departement");
                String spe = rs.getString("specialitecomplementaire");
                lesMeds.add(new Med(nom, prenom, adresse, tel, spe, id, dep));
            }
        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return lesMeds;




    }
}